package cn.gov.pbc.gd.credit.common.util;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.gov.pbc.gd.credit.vo.log.LogInquiryVo;

public class ExcelUtil {
	 public static  byte[]  exportDataToExcel(List<LogInquiryVo> LogInquiryVoList, String titleName){
	        byte[] bytes = null;
	        try{
	            //1.创建工作簿
	            HSSFWorkbook workbook = new HSSFWorkbook();
	           // ByteArrayOutputStream out = new ByteArrayOutputStream();
	            //1.1创建合并单元格对象
	            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,10);//起始行,结束行,起始列,结束列
	            //1.2头标题样式
	            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
	            //1.3列标题样式
	            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
	            //2.创建工作表
	            HSSFSheet sheet = workbook.createSheet(titleName);
	            //2.1加载合并单元格对象
	            sheet.addMergedRegion(callRangeAddress);
	            //设置默认列宽
	            sheet.setDefaultColumnWidth(18);
	            //sheet.autoSizeColumn(1); 
	            //3.创建行
	            //3.1创建头标题行;并且设置头标题
	            HSSFRow row = sheet.createRow(0);
	            HSSFCell cell = row.createCell(0);
	        
	            //加载单元格样式
	            cell.setCellStyle(headStyle);
	            cell.setCellValue(titleName);
	            cell.setCellValue(titleName);
	            
	            //3.2创建列标题;并且设置列标题
	            HSSFRow row2 = sheet.createRow(1);
	            String[] titles = {"所属机构","被查人员","身份证号","查询时间","查询结果","查询类型","报告编号","操作人"};
	            for(int i=0;i<titles.length;i++) {
	                HSSFCell cell2 = row2.createCell(i);
	                //加载单元格样式
	                cell2.setCellStyle(colStyle);
	                cell2.setCellValue(titles[i]);
	            }
	            
	            
	            //4.操作单元格;将用户列表写入excel
	            if(LogInquiryVoList != null) {
	                for(int j=0;j<LogInquiryVoList.size();j++) {
	                    //创建数据行,前面有两行,头标题行和列标题行
	                    HSSFRow row3 = sheet.createRow(j+2);
	                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	                    HSSFCell cell0 = row3.createCell(0);
	                    if (LogInquiryVoList.get(j).getCorpId()!= null) {//所属机构
	                    	cell0.setCellValue(LogInquiryVoList.get(j).getCorpId());
						}else{
							cell0.setCellValue("");
						}
	                    HSSFCell cell1 = row3.createCell(1);
	                    if (LogInquiryVoList.get(j).getUserName() != null) {//被查人员
	                    	cell1.setCellValue(LogInquiryVoList.get(j).getUserName());
						}else{
							cell1.setCellValue("");
						}
	                    HSSFCell cell2 = row3.createCell(2);
	                    if (LogInquiryVoList.get(j).getCardNum() != null) {//身份证号
	                    	cell2.setCellValue(LogInquiryVoList.get(j).getCardNum());
						}else{
							cell2.setCellValue("");
						}
	                    HSSFCell cell3 = row3.createCell(3);
	                    if (LogInquiryVoList.get(j).getCreateTime()!=null) { //查询时间
	                    	cell3.setCellValue(sdf.format(LogInquiryVoList.get(j).getCreateTime())); 
	                	}else { 
	                		cell3.setCellValue(""); 
	            		}
	                    HSSFCell cell4 = row3.createCell(4);
	                    if(LogInquiryVoList.get(j).getQueryResult()!=null){//查询结果
	                    	if("0".equals(LogInquiryVoList.get(j).getQueryResult())){
	                    		cell4.setCellValue("未完成");
	                    	}else{
	                    		cell4.setCellValue("已完成");
	                    	}
						}else{
							cell4.setCellValue("");
						}
	                    HSSFCell cell5 = row3.createCell(5);
	                    if (LogInquiryVoList.get(j).getInquiryType() != null) {//查询类型
	                    	if("1".equals(String.valueOf(LogInquiryVoList.get(j).getInquiryType()))){
	                    		cell5.setCellValue("个人查询");
	                    	}else if("2".equals(String.valueOf(LogInquiryVoList.get(j).getInquiryType()))){
	                    		cell5.setCellValue("代理查询");
	                    	}else{
	                    		cell5.setCellValue("司法查询");
	                    	}
						}else{
							cell5.setCellValue("");
						}
	                    HSSFCell cell6 = row3.createCell(6);
	                    if (LogInquiryVoList.get(j).getReportCode() != null) {//报告编号
	                        	 cell6.setCellValue(LogInquiryVoList.get(j).getReportCode());
						}else{
							cell6.setCellValue("");
						}
	                    HSSFCell cell7 = row3.createCell(7);
	                    if (LogInquiryVoList.get(j).getAccount() != null) {
	                    	cell7.setCellValue(LogInquiryVoList.get(j).getAccount());
						}else{
							cell7.setCellValue("");
						}
	                }
	            }
	            ByteArrayOutputStream out = new ByteArrayOutputStream();
	            //5.输出
	            workbook.write(out);
	            bytes=out.toByteArray();
		        out.flush();
		        out.close();
	        }catch(Exception e) {
	            throw new RuntimeException("导出excel数据异常");
	        }
			return bytes;
	    }
	 
	  /**
	     * 
	     * @param workbook
	     * @param fontsize
	     * @return 单元格样式
	     */
	    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {
	        // TODO Auto-generated method stub
	        HSSFCellStyle style = workbook.createCellStyle();
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
	        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
	        //创建字体
	        HSSFFont font = workbook.createFont();
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	        font.setFontHeightInPoints(fontsize);
	        //加载字体
	        style.setFont(font);
	        return style;
	    }
}
